package jdbc;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author lzy
 * @create 2018-07-31 上午10:28
 */
public class TransactionTest {
    private static final Logger LOGGER = LoggerFactory.getLogger(TransactionTest.class);

    /**
     * 事务的隔离级别
     * 在JDBC可以通过Connection的setTransaction()设置隔离级别
     */
    @Test
    public void testTransactionIsolationUpdate() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            LOGGER.info("default isolation level:" + connection.getTransactionIsolation());
            connection.setAutoCommit(false);

            String sql = "UPDATE users SET balance=balance-500 WHERE id=?";
            update(connection, sql, 1);

            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
    }

    @Test
    public void testTransactionIsolationRead() {
        String sql = "SELECT balance FROM users WHERE id=?";
        Integer balance = getForValue(sql, 1);
        LOGGER.info("balance:" + balance);
    }

    @SuppressWarnings("unchecked")
    public <E> E getForValue(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;

        E result = null;

        try {
            connection = JdbcUtils.getConnection();
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 1. 取出的结果集应该只有一行，且只有一列
            resultset = preparedStatement.executeQuery();

            // 2.取得结果集中的数据
            if (resultset.next()) {
                result = (E) resultset.getObject(1);
            }

        } catch (Exception e) {
            LOGGER.error("error", e);
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultset);
        }

        return result;
    }

    /**
     * Tom给Jerry汇款500元
     * <p>
     * 关于事务：
     * 1. 如果多个操作，每个操作使用的是自己的单独的连接，则无法保证事务
     * 2. 具体步骤：
     * 1) 事务操作开始前，开始事务：取消Connection的默认提交行为。
     * connection.setAutoCommit(false);
     * 2) 如果事务的操作成功，则提交事务：
     * connection.commit();
     * 3) 回滚事务：若出现异常，则在catch块中回滚事务：
     * connection.rollback()
     */
    @Test
    public void testTransaction() {
        Connection connection = null;

        try {
            connection = JdbcUtils.getConnection();
            // 开始事务:取消默认提交
            connection.setAutoCommit(false);

            String sql = "UPDATE users SET balance=balance-500 WHERE id=?";
            update(connection, sql, 1);

            int i = 10 / 0;

            sql = "UPDATE users SET balance=balance+500 WHERE id=?";
            update(connection, sql, 2);

            // 提交事务
            connection.commit();
        } catch (Exception e) {
            if (connection != null) {
                try {
                    // 回滚事务
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, null, null);
        }
    }

    public void update(Connection connection, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            LOGGER.error("error", e);
        } finally {
            JdbcUtils.release(null, preparedStatement);
        }
    }
}
